This invention relates to a method of displaying a spreadsheet so as to facilitate the identification of errors.
An electronic spreadsheet is a two-dimensional grid containing data and formulas that are entered in a manner allowing computer manipulation. Spreadsheets can be used for simple applications such as balancing a checkbook, to applications as complex as inventory control and pricing for a factory. The formulas relate the data and produce results. For example, FIG. 1, column d (row n) may result from the summation of column c (row n) and column b (row n). See that the result of 41 in d3 equals the sum of the 17 in b3 and the 24 in c3. This result may in turn be used as data in other formulas. For example, FIG. 1, row 5 may be the sum of rows 2, 3, and 4. See that the 60 in b5 equals the sum of the 24 in b2, the 17 in b3, and the 19 in b4. As more data and formulas are entered into the spreadsheet, the interrelations that occur become complex and difficult to understand. Unfortunately, the accuracy of the final result depends on correctly linking together the data of the spreadsheet with all the formulas. As the spreadsheet becomes larger and more complex, it is difficult to determine whether all the relations have been correctly entered.
There are many spreadsheets out in the marketplace today for example Lotus 1-2-3 (Lotus 1-2-3 Release 5 User""s Guide. Lotus Development Corporation, Copyright 1994). Generally values are entered into a spreadsheet by clicking on a particular cell. This causes its current formula (perhaps none) to be displayed in a bar 11 often at the top FIG. 1. The user can then begin to type the desired formula or data into the spreadsheet. The value of the formula is displayed in the current cell. Over time, spreadsheet developers have enhanced spreadsheets to be able to contain text from word processors, data from other programs, and even in some cases images from paint or draw programs. Also, many possibilities for displaying the results have been developed, e.g. pie charts, bar graphs. However, all these features rely on the fact that the base formulas correctly interrelate the different cells containing information.
Spreadsheet developers have taken some steps in the direction of aiding the user in getting the crucial step or correctly defining cell interrelations. It is now possible to cut and paste formulas, and the spreadsheet automatically attempts to update the formulas to fit in the new location. There are also techniques to apply a given formula over a whole range of cells, where the data in the cells are updated as appropriate for the specific rows and columns that the formula has been applied to.
Currently, debugging a spreadsheet to find an error is difficult because users can not easily or visually see how the cells interrelate. While techniques that reduce the time taken to make a spreadsheet and reduce the possibility of making an error have been implemented, there is still a great need for techniques that help a user determine if the dependencies between cells they have entered into the spreadsheet are correct. There is also the need to help users more quickly track down the reason for an error when they discover one in their spreadsheet.
It is an object of this invention to allow the users to visually determine the relationship between the different cells in the spreadsheet.
It is another object of this invention to facilitate creating a error free spreadsheet With this invention, in a spreadsheet, all cells having data that depends on or affects the data in a user selected cell are visually indicated on a display. For example, in FIG. 2, if d2 (see bold rectangle surrounding the formula in d2) is chosen, and since cell d5 depends on d2, and since d2 depends on b2 and c2, cells d5, b2, and c2 are shaded. Cells farther away are shaded with a lighter color to indicate their increased distance from the selected cell in the dependency tree. The user can control both the number of levels of dependency relations to display as well as the branching factor (See below.) for the relations. The relations for a chosen cell become visually obvious to the user, who can then determine if the correct relations have been entered into the spreadsheet. Using this invention greatly simplifies the users"" task of ensuring that all the proper relations between cells have been established in the spreadsheet. This invention also simplifies finding an error if the final result(s) of the spreadsheet are not as expected.
Throughout this application, when it is said that one cell depends on another cell, it actually means that the data in the one cell is dependent on the data in the other cell. For cell B (for example) to be directly determined by cell C (for example), cell C must appear in the formula of cell B. Likewise, for cell B to directly determine cell A (for example), cell B must appear in the formula of cell A.
Also, with this invention, the formula of all the cells in a spreadsheet can be displayed to facilitate error detection.